﻿package flight.db.activeRecord
{
	import flash.data.SQLColumnSchema;
	import flash.data.SQLSchemaResult;
	import flash.data.SQLStatement;
	import flash.data.SQLTableSchema;

	import flight.db.DB;
	import flight.db.sql_db;
	import flight.utils.Reflection;

	use namespace sql_db;


	public class TableCreator{
		private static var tablesUpdated:Object = {};


		// テーブルが存在しなければ作成、存在するなら更新する。
		static public function updateTable( obj:ActiveRecord, schema:SQLTableSchema = null  ):void{
			if( checkTableExsistent(obj) == true ){
				//trace( obj.className +"のテーブルは存在するので更新します。" );
				update( obj, schema );
			} else {
				//trace( obj.className +"のテーブルが存在しないので作成します。" );
				create( obj, schema );
			}
		}

		// 指定したモデルのテーブルが存在するかチェック
		static public function checkTableExsistent( obj:ActiveRecord ):Boolean{
			var tableName:String = ActiveRecord.schemaTranslation.getTable( obj.className );
			try{
				obj.connection.loadSchema();
				var schema:SQLSchemaResult =  obj.connection.getSchemaResult();
				for each( var table:SQLTableSchema in schema.tables ){
					if( table.name.toLowerCase() == tableName.toLowerCase() ){
						return true;
					}
				}
			} catch(e){
				// DBが存在しません。
			}
			return false;
		}





		// テーブル作成
		public static function create( obj:ActiveRecord, schema:SQLTableSchema = null ):void{
			var tableName:String = ActiveRecord.schemaTranslation.getTable(obj.className);
			var primaryKey:String = ActiveRecord.schemaTranslation.getPrimaryKey(obj.className);

			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = obj.connection;
			var sql:String;

			// get all this object's properties we want to store in the database
			var def:XML = Reflection.describe(obj);
			var publicVars:XMLList = def.*.(
					(
						localName() == "variable" ||
						(localName() == "accessor" && @access == "readwrite")
					)
					&&
					(
						@type == "String" ||
						@type == "Number" ||
						@type == "Boolean" ||
						@type == "uint" ||
						@type == "int" ||
						@type == "Date" ||
						@type == "flash.utils.ByteArray"
					)
				);

			var field:XML;
			var fieldDef:Array

			var fields:Array = [];

			for each (field in publicVars){
				fieldDef = [field.@name, dbTypes[field.@type]];

				if (field.@name == primaryKey){
					fieldDef.push("PRIMARY KEY AUTOINCREMENT");
				}
				fields.push(fieldDef.join(" "));
			}
			obj.connection.begin();
			sql = "CREATE TABLE " + tableName + " (" + fields.join(", ") + ")";
			stmt.text = sql;
			stmt.execute();

			if (obj.connection.inTransaction){
				obj.connection.commit();
			}
		}




		// テーブル更新
		public static function update( obj:ActiveRecord, schema:SQLTableSchema = null ):void{
			var tableName:String = ActiveRecord.schemaTranslation.getTable(obj.className);
			var primaryKey:String = ActiveRecord.schemaTranslation.getPrimaryKey(obj.className);

			var stmt:SQLStatement = new SQLStatement();
			stmt.sqlConnection = obj.connection;
			var sql:String;

			// get all this object's properties we want to store in the database
			var def:XML = Reflection.describe(obj);
			var publicVars:XMLList = def.*.(
					(
						localName() == "variable" ||
						(localName() == "accessor" && @access == "readwrite")
					)
					&&
					(
						@type == "String" ||
						@type == "Number" ||
						@type == "Boolean" ||
						@type == "uint" ||
						@type == "int" ||
						@type == "Date" ||
						@type == "flash.utils.ByteArray"
					)
				);

			var field:XML;
			var fieldDef:Array

			if (!schema){
				try {
					var dbschema:SQLSchemaResult = DB.getSchema(obj.connection);
				} catch (e:Error) {
					trace( "TableCreator.updateTable "+ e );
				}
				var schema:SQLTableSchema;

				// first, find the table this object represents
				if (dbschema){
					for each (var tmpTable:SQLTableSchema in dbschema.tables){
						if (tmpTable.name == tableName){
							schema = tmpTable;
							break;
						}
					}
				}
			}

			// if no table was found, create it, otherwise, update any missing fields
			if( schema ){
				for each (field in publicVars){
					var found:Boolean = false;
					for each (var column:SQLColumnSchema in schema.columns){
						if (column.name == field.@name){
							found = true;
							break;
						}
					}
					if (found){
						continue;
					}

					// add the field to be created
					fieldDef = ["ADD", field.@name, dbTypes[field.@type]];

					obj.connection.begin();
					sql = "ALTER TABLE " + tableName + " " + fieldDef.join(" ");
					stmt.text = sql;
					stmt.execute();
				}
			}

			if (obj.connection.inTransaction){
				obj.connection.commit();
			}
			trace( "テーブル「"+ tableName +"」の更新が完了しました。" );
		}



		sql_db static var dbTypes:Object = {
			"String": "VARCHAR",
			"Number": "DOUBLE",
			"Boolean": "BOOLEAN",
			"uint": "INTEGER",
			"int": "INTEGER",
			"Date": "DATETIME",
			"flash.utils.ByteArray": "BLOB"
		};
	}
}
